import mysql from 'mysql2/promise';

export interface DatabaseConfig {
  host: string;
  port: number;
  user: string;
  password: string;
  database: string;
}

// 获取默认数据库配置（运行时读取环境变量）
function getDefaultConfig(): DatabaseConfig {
  return {
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '3306'),
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'root',
    database: process.env.DB_NAME || 'ribao_config'
  };
}

let connection: mysql.Connection | null = null;
let isConnected = false;

/**
 * 测试数据库连接
 */
export async function testConnection(config: DatabaseConfig = getDefaultConfig()): Promise<boolean> {
  try {
    console.log('🔍 数据库连接配置:', {
      host: config.host,
      port: config.port,
      user: config.user,
      password: config.password ? '***' : 'undefined',
      database: config.database
    });
    const testConnection = await mysql.createConnection(config);
    await testConnection.ping();
    await testConnection.end();
    return true;
  } catch (error) {
    console.log('数据库连接测试失败:', error);
    return false;
  }
}

/**
 * 初始化数据库连接
 */
export async function initDatabase(config: DatabaseConfig = getDefaultConfig()): Promise<boolean> {
  try {
    // 测试连接
    const canConnect = await testConnection(config);
    if (!canConnect) {
      console.log('数据库连接失败，将使用文件存储');
      return false;
    }

    // 创建连接
    connection = await mysql.createConnection(config);
    
    // 创建数据库（如果不存在）
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${config.database}`);
    await connection.query(`USE ${config.database}`);
    
    // 创建配置表
    await createConfigTable();
    
    isConnected = true;
    console.log('数据库连接成功');
    return true;
  } catch (error) {
    console.error('数据库初始化失败:', error);
    isConnected = false;
    return false;
  }
}

/**
 * 创建配置表和项目表
 */
async function createConfigTable(): Promise<void> {
  if (!connection) throw new Error('数据库未连接');
  
  // 创建配置表
  const createConfigTableSQL = `
    CREATE TABLE IF NOT EXISTS app_config (
      id INT PRIMARY KEY AUTO_INCREMENT,
      config_key VARCHAR(255) UNIQUE NOT NULL,
      config_value JSON NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
  `;
  
  // 创建项目表
  const createProjectsTableSQL = `
    CREATE TABLE IF NOT EXISTS projects (
      id VARCHAR(50) PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      path TEXT NOT NULL,
      description TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX idx_created_at (created_at),
      INDEX idx_name (name)
    )
  `;
  
  await connection.execute(createConfigTableSQL);
  await connection.execute(createProjectsTableSQL);
  
  console.log('数据库表创建完成');
}

/**
 * 获取数据库连接
 */
export function getConnection(): mysql.Connection | null {
  return connection;
}

/**
 * 检查数据库是否已连接
 */
export function isDatabaseConnected(): boolean {
  return isConnected;
}

/**
 * 关闭数据库连接
 */
export async function closeConnection(): Promise<void> {
  if (connection) {
    await connection.end();
    connection = null;
    isConnected = false;
  }
}

/**
 * 获取默认配置
 */
export { getDefaultConfig };